﻿#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PhotoGallery.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace PhotoGallery.DALProviders.SqlServerProvider
{
    public class LocationProvider : PhotoGallery.Core.Providers.DALProvider.ILocationProvider
    {
        #region Sql
        private static readonly string SqlSelectLocation = "SELECT [Id], [Name],[Description] FROM [Location] WHERE  [Id]=@Id;";

        private static readonly string SqlInsertLocation = "INSERT INTO [Location]([Name],[Description]) values(@Name,@Description);SELECT SCOPE_IDENTITY();";

        private static readonly string SqlUpdateLocation = "UPDATE [Location] SET [Name] = @Name,[Description] = @Description WHERE  [Id]=@Id;";

        private static readonly string SqlDeleteLocation = "DELETE [Location] WHERE  [Id]=@Id;";

        private static readonly string SqlGetAllLocation = "SELECT [Id], [Name],[Description] FROM [Location];";


        private static readonly string SqlGetLocation = "SELECT [Id], [Name],[Description] FROM [Location] where [Name] like '%'+@name+'%';";

        #endregion

        #region Procedures
        /*

-- Begin of Location
IF OBJECT_ID(N'[USP_Location_Delete_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_Location_Delete_By_Id]

IF OBJECT_ID(N'[USP_Location_Select_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_Location_Select_By_Id]

IF OBJECT_ID(N'[USP_Location_Insert]') IS NOT NULL
	DROP PROCEDURE [USP_Location_Insert]

IF OBJECT_ID(N'[USP_Location_Update_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_Location_Update_By_Id]

IF OBJECT_ID(N'[USP_Location_SelectAll]') IS NOT NULL
	DROP PROCEDURE [USP_Location_SelectAll]

GO

--region [USP_Location_Delete_By_Id]
CREATE PROCEDURE [USP_Location_Delete_By_Id]
	@Id	int
AS
SET NOCOUNT ON
DELETE [Location] WHERE  [Id]=@Id;
--endregion
GO

--region [USP_Location_Select_By_Id]
CREATE PROCEDURE [USP_Location_Select_By_Id]
	@Id	int
AS
SET NOCOUNT ON
SELECT [Id], [Name],[Description] FROM [Location] WHERE  [Id]=@Id;
--endregion
GO

--region [USP_Location_Insert]
CREATE PROCEDURE [USP_Location_Insert]
	@Name	nvarchar(50),
	@Description	nvarchar(100)
AS
SET NOCOUNT ON
INSERT INTO [Location]([Name],[Description]) values(@Name,@Description);SELECT SCOPE_IDENTITY();
--endregion
GO

--region [USP_Location_Update_By_Id]
CREATE PROCEDURE [USP_Location_Update_By_Id]
	@Name	nvarchar(50),
	@Description	nvarchar(100),
	@Id	int
AS
SET NOCOUNT ON
UPDATE [Location] SET [Name] = @Name,[Description] = @Description WHERE  [Id]=@Id;
--endregion
GO

--region [USP_Location_SelectAll]
CREATE PROCEDURE [USP_Location_SelectAll]
AS
SET NOCOUNT ON
SELECT [Id], [Name],[Description] FROM [Location];
--endregion
GO


-- End of Location
*/
        #endregion

        #region ILocationProvider Members
        public IList<Location> GetAll()
        {
            return GetAllLocation();
        }
        public Core.Business.Location Select(int id)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Id", SqlDbType.Int, id);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectLocation);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.Location location = new Core.Business.Location();

                if (!reader.IsDBNull(0)) location.Id = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) location.Name = reader.GetString(1);
                if (!reader.IsDBNull(2)) location.Description = reader.GetString(2);

                reader.Close();
                return location;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public void Insert(Core.Business.Location location)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Name", SqlDbType.NVarChar, location.Name);
            sql.AddParameter("@Description", SqlDbType.NVarChar, location.Description);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertLocation);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                if (!reader.IsDBNull(0)) location.Id = int.Parse(reader.GetDecimal(0).ToString());

                reader.Close();
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }
        }

        public void Update(Core.Business.Location location)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Name", SqlDbType.NVarChar, location.Name);
            sql.AddParameter("@Description", SqlDbType.NVarChar, location.Description);
            sql.AddParameter("@Id", SqlDbType.Int, location.Id);
            sql.ExecuteSql(SqlUpdateLocation);
        }

        public void Delete(Core.Business.Location location)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Id", SqlDbType.Int, location.Id);
            sql.ExecuteSql(SqlDeleteLocation);
        }

        public IList<Core.Business.Location> GetAllLocation()
        {
            IList<Core.Business.Location> locationlist = new List<Core.Business.Location>();
            SqlServerUtility sql = new SqlServerUtility();

            SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllLocation);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.Location location = new Core.Business.Location();

                    if (!reader.IsDBNull(0)) location.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) location.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2)) location.Description = reader.GetString(2);

                    location.MarkOld();
                    locationlist.Add(location);
                }
                reader.Close();
            }
            return locationlist;
        }
        #endregion

        #region Search
        public ICollection<Location> Search(string s)
        {
            IList<Core.Business.Location> locationlist = new List<Core.Business.Location>();

            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@name", SqlDbType.NVarChar, s);

            SqlDataReader reader = sql.ExecuteSqlReader(SqlGetLocation);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.Location location = new Core.Business.Location();

                    if (!reader.IsDBNull(0)) location.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) location.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2)) location.Description = reader.GetString(2);

                    location.MarkOld();
                    locationlist.Add(location);
                }
                reader.Close();
            }
            return locationlist;
        }
        #endregion


    }
}
